package db.jdbc;

import db.SystemAdministratorRepository;
import domain.SystemAdministrator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 系统管理员资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcSystemAdministratorRepository implements SystemAdministratorRepository {
    private static final String INSERT_SystemAdministrator = "insert into systemAdministrator(username,_password,nickname,telephone,email,createTime) values(?,?,?,?,?,?)";
    private static final String DELETE_SystemAdministrator = "delete from systemAdministrator where systemAdminID=?";
    private static final String UPDATE_SystemAdministrator = "update systemAdministrator set username=?,_password=?,nickname=?,telephone=?,email=?";
    private static final String UPDATE_Password = "update systemAdministrator set _password=? where username=?";

    private static final String SELECT_SystemAdministrator = "select * from systemAdministrator";
    private static final String SELECT_SystemAdministrator_Pagination = SELECT_SystemAdministrator + " order by systemAdminID limit ? offset  ?";

    private JdbcTemplate jdbc;

    @Autowired
    public JdbcSystemAdministratorRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取系统管理员的总数
     *
     * @return 系统管理员的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(systemAdminID) from systemAdministrator");
    }

    /**
     * 创建一个新的系统管理员
     *
     * @param systemAdministrator 新系统管理员的信息
     * @return 新创建的系统管理员
     */
    @Override
    public SystemAdministrator save(SystemAdministrator systemAdministrator) {
        jdbc.update(INSERT_SystemAdministrator,
                systemAdministrator.getUsername(),
                systemAdministrator.getPassword(),
                systemAdministrator.getNickname(),
                systemAdministrator.getTelephone(),
                systemAdministrator.getEmail(),
                systemAdministrator.getCreateTime());
        return systemAdministrator;
    }

    /**
     * 根据ID查找系统管理员
     *
     * @param systemAdminId 系统管理员的ID
     * @return 对应ID的系统管理员
     */
    @Override
    public SystemAdministrator findOne(int systemAdminId) {
        SystemAdministrator systemAdministrator = null;
        try {
            systemAdministrator = jdbc.queryForObject(SELECT_SystemAdministrator + " where systemAdminID=?", new SystemAdministratorRowMapper(), systemAdminId);
        } catch (DataAccessException e) {
        }
        return systemAdministrator;
    }

    /**
     * 根据用户名查找系统管理员
     *
     * @param userName 用户名
     * @param password 密码
     * @return 对应用户名的系统管理员 system administrator
     */
    @Override
    public SystemAdministrator findByUserName(String userName, String password) {
        SystemAdministrator systemAdministrator = null;
        try {
            systemAdministrator = jdbc.queryForObject(SELECT_SystemAdministrator + " where username=? and _password=?", new SystemAdministratorRowMapper(), userName, password);
        } catch (DataAccessException e) {
        }
        return systemAdministrator;
    }

    /**
     * 分页查找系统管理员
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 分页的系统管理员列表
     */
    @Override
    public PaginationSupport<SystemAdministrator> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<SystemAdministrator>(new ArrayList<SystemAdministrator>(0), 0);
        }

        List<SystemAdministrator> items = jdbc.query(SELECT_SystemAdministrator_Pagination, new SystemAdministratorRowMapper(), pageSize, startIndex);
        return new PaginationSupport<SystemAdministrator>(items, totalCount, pageSize, startIndex);
    }

    /**
     * 更新系统管理员信息
     *
     * @param systemAdminID 系统管理员ID
     * @param userName      用户名
     * @param password      密码
     * @param email         邮箱
     * @param nickname      昵称
     * @param telephone     电话
     * @return 是否更新成功 boolean
     */
    @Override
    public boolean updateSystemAdministrator(int systemAdminID, String userName, String password, String email, String nickname, String telephone) {
        return jdbc.update(UPDATE_SystemAdministrator + " where systemAdminID=?", userName, password, nickname, telephone, email, systemAdminID) != 0;
    }

    /**
     * 更新系统管理员的密码
     *
     * @param userName 系统管理员用户名
     * @param Password 新密码
     * @return 是否更新成功
     */
    @Override
    public boolean updatePassword(String userName, String Password) {
        return jdbc.update(UPDATE_Password, Password, userName) != 0;
    }

    /**
     * 获取所有系统管理员
     *
     * @return 所有系统管理员的列表
     */
    @Override
    public List<SystemAdministrator> findAll() {
        return jdbc.query(SELECT_SystemAdministrator+" order by systemAdminID",new SystemAdministratorRowMapper());
    }

    static class SystemAdministratorRowMapper implements RowMapper<SystemAdministrator> {

        /**
         * 将数据库查询结果映射到User对象
         *
         * @param rs 结果集
         * @param i 行号
         * @return User对象
         * @throws SQLException SQL异常
         */
        public SystemAdministrator mapRow(ResultSet rs, int i) throws SQLException {
            return new SystemAdministrator(rs.getString("username"),
                    rs.getString("_password"),
                    rs.getString("nickname"),
                    rs.getString("telephone"),
                    rs.getString("email"),
                    rs.getTimestamp("createTime"),
                    rs.getInt("systemAdminID"));
        }
    }
}
